import numpy as np
import pymysql
import pandas as pd

config = {
          'host': '192.168.3.172',
          'port': 3306,
          'user': 'django',
          'password':'Abc123',
          'db':'TEST',
          'charset':'utf8mb4',
          'cursorclass':pymysql.cursors.DictCursor,
          }
connection = pymysql.connect(**config)

# 道生一：传入type
class SayMetaClass(type):
    # 传入三大永恒命题：类名称、父类、属性
    def __new__(mcs, name, bases, attrs):
        # 创造“天赋”
        attrs['say_' + name] = lambda self, value, saying=name: print(saying + ',' + value + '!')
        print(attrs)
        # 传承三大永恒命题：类名称、父类、属性
        return type.__new__(mcs, name, bases, attrs)


# 一生二：创建类
# class Hello(object, metaclass=SayMetaClass):
#     pass
#
#
# class Sayolala(object, metaclass=SayMetaClass):
#     pass


# def test_say():
#     # 二生三：创建实列
#     hello = Hello()
#
#     # 三生万物：调用实例方法
#     hello.say_Hello('world!')
#
#     olala = Sayolala()
#     olala.say_Sayolala('olll')


class Field(object):

    def __init__(self, name, column_type):
        self.name = name
        self.column_type = column_type

    def __str__(self):
        return '<%s:%s>' % (self.__class__.__name__, self.name)


# 创建StringField和IntergerField
class StringField(Field):

    def __init__(self, name):
        super(StringField, self).__init__(name, 'varchar(100)')


class IntegerField(Field):

    def __init__(self, name):
        super(IntegerField, self).__init__(name, 'bigint')


# Model元类
class ModelMetaclass(type):

    def __new__(mcs, name, bases, attrs):
        if name == 'Model':
            return type.__new__(mcs, name, bases, attrs)
        print('Found model: %s' % name)
        mappings = dict()

        for k, v in attrs.items():
            if isinstance(v, Field):
                print('Found mapping: %s ==> %s' % (k, v))
                mappings[k] = v

        for k in mappings.keys():
            attrs.pop(k)

        attrs['__mappings__'] = mappings  # 保存属性和列的映射
        attrs['__table__'] = name  # 表明

        return type.__new__(mcs, name, bases, attrs)


class Model(dict, metaclass=ModelMetaclass):

    def __init__(self, **kwargs):
        super(Model, self).__init__(**kwargs)

    def __getattr__(self, item):
        try:
            return self[item]
        except KeyError:
            raise AttributeError("'model' object has no attribute '%s'" % item)

    def __setattr__(self, key, value):
        print('key : %s, value: %%' % key, value)
        self[key] = value

    def save(self):
        fields = []
        args = []
        for k, v in self.__mappings__.items():
            fields.append(v.name)
            args.append(getattr(self, k, None))

        sql = 'INSERT INTO %s(%s) VALUES (%s)' % (self.__table__, ','.join(fields), ','.join(
            ["'%s'" % str(i) for i in args]
        ))
        print('SQL: %s' % sql)
        try:
            with connection.cursor() as cursor:
                cursor.execute(sql)

            # 主动提交，以保存所执行的语句
            connection.commit()
        except Exception as e:
            print(e)
        print('ARGS: %s' % str(args))

    def filter(self, **kwargs):
        # 是否有order_by
        order_by = ''
        if kwargs.get('order_by'):
            order_by = "ORDER BY %s" % ','.join(kwargs['order_by'])
            kwargs.pop('order_by')
        if kwargs.get('order_by_desc'):
            order_by = "ORDER BY %s" % ' DESC,'.join(kwargs['order_by_desc'])
            kwargs.pop('order_by_desc')
        fields = []
        for k, v in kwargs.items():
                if '_' in k:
                    tmp_words = k.split('_')
                    key = '_'.join(tmp_words[:-1])
                    if key in self.__mappings__.keys() or key == 'id':
                        func = tmp_words[-1]
                        if func == 'contains':
                            fields.append("{key} like '%{value}%'".format(key=key, value=v))
                        if func == 'lte':
                            fields.append("{key} < {value}".format(key=key, value=v))
                        if func == 'gte':
                            fields.append("{key} > {value}".format(key=key, value=v))
                    else:
                        raise AttributeError("'model' object has no attribute '%s'" % key)
                else:
                    if k in self.__mappings__.keys() or key == 'id':
                        fields.append("%s='%s'" % (k, v))
                    else:
                        raise AttributeError("'model' object has no attribute '%s'" % k)

        sql = 'SELECT * From %s WHERE %s %s' % (self.__table__, ' and '.join(fields), order_by)
        print(sql)
        try:
            return pd.read_sql(sql, con=connection)
        except Exception as e:
            print(e)
        return None


class User(Model):
    name = StringField('name')
    email = StringField('email')
    passwd = StringField('passwd')


class Job(Model):
    job_name = StringField('name')
    price = IntegerField('price')
    total_num = IntegerField('total')


def save_test():
    j = Job(job_name='My Job', price=500, total_num=1000)
    j.save()

    u = User(name='11xoos', email='32ccc23@163.com', passwd='xxssxx')
    u.save()

if __name__ == '__main__':
    # save_test()
    u = User().filter(email_contains='@', order_by_desc=['id', 'name'])
    # U 返回DataFate， 继续做逻辑处理，
    # 最后返回Json结构数据
    res = u.to_json(orient='records')
    print({'data': res, 'status': 0, 'message': 'OK'})

    connection.close()

